package com.lly.tboc.zhouyi.db

import android.content.Context
import androidx.room.Dao
import androidx.room.Database
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase
import com.lly.tboc.zhouyi.vo.Gua
import com.lly.tboc.zhouyi.vo.GuaFts
import com.lly.tboc.zhouyi.vo.WenYan
import com.lly.tboc.zhouyi.vo.Yao
import com.lly.tboc.zhouyi.vo.YaoFts
import kotlinx.coroutines.flow.Flow


@Database(
    entities = [Gua::class, Yao::class, WenYan::class, GuaFts::class, YaoFts::class],
    version = 10,
    exportSchema = true
)
abstract class ZhouyiDb : RoomDatabase() {
    companion object {
        val DB_NAME = "zhouyi.db"
        fun create(context: Context, useInMemory: Boolean = false): ZhouyiDb {
            val databaseBuilder = if (useInMemory) {
                Room.inMemoryDatabaseBuilder(context, ZhouyiDb::class.java)
            } else {
                Room.databaseBuilder(
                    context, ZhouyiDb::class.java,
                    DB_NAME
                ).addMigrations(*MIGRATION_ALL)
            }
            if (!useInMemory) {
                databaseBuilder.createFromAsset("db/zhouyi.db")
            }
            return databaseBuilder

                .build()
        }

        val MIGRATION_9_10 = object : Migration(9, 10) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("DROP TABLE IF EXISTS `yao`")
                db.execSQL("DROP TABLE IF EXISTS `gua`")
                db.execSQL("DROP TABLE IF EXISTS `wenYan`")
                db.execSQL("DROP TABLE IF EXISTS `guafts`")
                db.execSQL("DROP TABLE IF EXISTS `yaofts`")
                db.execSQL("CREATE TABLE IF NOT EXISTS `gua` (`id` INTEGER NOT NULL, `name` TEXT NOT NULL, `outer` TEXT NOT NULL, `inner` TEXT NOT NULL, `ci` TEXT NOT NULL, `xiang` TEXT, `tuan` TEXT, `wu` TEXT, PRIMARY KEY(`id`))")
                db.execSQL("CREATE TABLE IF NOT EXISTS `yao` (`id` INTEGER NOT NULL, `guaId` INTEGER NOT NULL, `name` TEXT NOT NULL, `ci` TEXT NOT NULL, `xiang` TEXT, `wu` TEXT, PRIMARY KEY(`id`, `guaId`))")
                db.execSQL("CREATE INDEX IF NOT EXISTS `index_yao_guaId` ON `yao` (`guaId`)")
                db.execSQL("CREATE TABLE IF NOT EXISTS `wenYan` (`guaId` INTEGER NOT NULL, `wenYan` TEXT NOT NULL, PRIMARY KEY(`guaId`))")
                db.execSQL("CREATE VIRTUAL TABLE IF NOT EXISTS `guafts` USING FTS4(`name` TEXT NOT NULL, `ci` TEXT NOT NULL, `wu` TEXT, tokenize=unicode61, content=`gua`)")
                db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_guafts_BEFORE_UPDATE BEFORE UPDATE ON `gua` BEGIN DELETE FROM `guafts` WHERE `docid`=OLD.`rowid`; END")
                db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_guafts_BEFORE_DELETE BEFORE DELETE ON `gua` BEGIN DELETE FROM `guafts` WHERE `docid`=OLD.`rowid`; END")
                db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_guafts_AFTER_UPDATE AFTER UPDATE ON `gua` BEGIN INSERT INTO `guafts`(`docid`, `name`, `ci`, `wu`) VALUES (NEW.`rowid`, NEW.`name`, NEW.`ci`, NEW.`wu`); END")
                db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_guafts_AFTER_INSERT AFTER INSERT ON `gua` BEGIN INSERT INTO `guafts`(`docid`, `name`, `ci`, `wu`) VALUES (NEW.`rowid`, NEW.`name`, NEW.`ci`, NEW.`wu`); END")
                db.execSQL("CREATE VIRTUAL TABLE IF NOT EXISTS `yaofts` USING FTS4(`id` INTEGER NOT NULL, `guaId` INTEGER NOT NULL, `name` TEXT NOT NULL, `ci` TEXT NOT NULL, `wu` TEXT, tokenize=unicode61, content=`yao`)")
                db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_yaofts_BEFORE_UPDATE BEFORE UPDATE ON `yao` BEGIN DELETE FROM `yaofts` WHERE `docid`=OLD.`rowid`; END")
                db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_yaofts_BEFORE_DELETE BEFORE DELETE ON `yao` BEGIN DELETE FROM `yaofts` WHERE `docid`=OLD.`rowid`; END")
                db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_yaofts_AFTER_UPDATE AFTER UPDATE ON `yao` BEGIN INSERT INTO `yaofts`(`docid`, `id`, `guaId`, `name`, `ci`, `wu`) VALUES (NEW.`rowid`, NEW.`id`, NEW.`guaId`, NEW.`name`, NEW.`ci`, NEW.`wu`); END")
                db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_yaofts_AFTER_INSERT AFTER INSERT ON `yao` BEGIN INSERT INTO `yaofts`(`docid`, `id`, `guaId`, `name`, `ci`, `wu`) VALUES (NEW.`rowid`, NEW.`id`, NEW.`guaId`, NEW.`name`, NEW.`ci`, NEW.`wu`); END")
                db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)")
                db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '018ecade4cd6f321134ec533e958432a')")
            }
        }
        val MIGRATION_ALL = arrayOf(MIGRATION_9_10)
    }

    abstract fun guaDao(): GuaDao
    abstract fun yaoDao(): YaoDao
    abstract fun searchDao(): SearchDao
}

@Dao
interface GuaDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(gua: List<Gua>)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(gua: Gua)

    @Delete
    fun delete(gua: List<Gua>)

    @Delete
    fun delete(gua: Gua)

    @Query("SELECT * FROM gua WHERE id = :id")
    suspend fun get(id: Int): Gua?


    @Query("SELECT * FROM gua WHERE name = :name")
    fun get(name: String): Gua?


    @Query("SELECT * FROM gua ORDER BY id ASC")
    fun get(): Flow<List<Gua>>

    @Query("SELECT count(id) FROM gua limit 1")
    fun isNotEmpty(): Int

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(wenYan: WenYan)

    @Query("SELECT * FROM wenYan WHERE guaId = :guaId")
    fun getWenYan(guaId: Int): WenYan?
}

@Dao
interface YaoDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(yao: List<Yao>)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(yao: Yao)

    @Delete
    fun delete(yao: Yao)

    @Query("SELECT * FROM yao WHERE guaId = :guaId order by id asc")
    suspend fun get(guaId: Int): List<Yao>


    @Query("SELECT * FROM yao WHERE guaId = :guaId AND id = :yaoId")
    suspend fun get(guaId: Int, yaoId: Int): Yao?

}

@Dao
interface SearchDao {
    @Query(
        """
      select DISTINCT gua.* from gua where gua.id in (select rowid from guafts where guafts match :query
            union
            select yaofts.guaId from yaofts where yaofts match :query
      ) 
        """
    )
    fun search(query: String): Flow<List<Gua>>

    @Query("select * from yao where ci like :query or name like :query or xiang like :query ")
    fun searchYaoByLike(query: String): List<Yao>
}